package database.punishte;

import database.common.ConnectionFactory;
import model.punishte.Konsum;
import model.punishte.Prodhim;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   1/3/13
 * Time:   8:10 PM
 */
public class ProdhimDao {
    private static final String PRODHIM_INSERT =
            "INSERT INTO punishte.prodhim (id,punetor_id, operator_id, produkt_id,nr_dozave, nr_njesive,prodhim_ideal,prodhim_fakt,kosto_fikse,kosto_extra,data_prodhimit,data_veprimit,punishte_id,magazina_id,shenime ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String PRODHIM_UPDATE =
            "UPDATE punishte.prodhim SET punetor_id=?, operator_id=?, produkt_id=?,nr_dozave=?, nr_njesive=?,prodhim_ideal=?,prodhim_fakt=?,kosto_fikse=?,kosto_extra=?,data_prodhimit=?,data_veprimit=?,punishte_id=?,magazina_id=?,shenime=? WHERE id  = ?";
    private static final String SELECT_SINGLE_PRODHIM =
            "SELECT id,punetor_id, operator_id, produkt_id,nr_dozave, nr_njesive,prodhim_ideal,prodhim_fakt,kosto_fikse,kosto_extra,data_prodhimit,data_veprimit,punishte_id,magazina_id,shenime FROM punishte.prodhim WHERE id = ? ";
    private static final String PRODHIM_KONSUM_INSERT =
            "INSERT INTO punishte.konsum (prodhim_id, artikull_id, sasia, cmimi) VALUES (?,?,?,?)";
    private static final String SELECT_PRODHIM_KONSUM =
            "SELECT id, prodhim_id, artikull_id, sasia, cmimi FROM punishte.konsum WHERE prodhim_id=?";
    private static final String SELECT_PRODHIME_BY_DATE =
            "SELECT query_to_xml" +
            "('SELECT * from punishte.prodhim WHERE punishte_id='||?||' AND date(data_prodhimit)>=date('||?||') AND date(data_prodhimit)<=date('||?||') ORDER BY data_prodhimit '," +
            "true,false,'')";

    private static int insert(Prodhim prodhim, Connection connection) throws SQLException {
        PreparedStatement statement = null;
        int rows = 0;
        if (connection == null || connection.isClosed()){
            return -1;
        }
        try {
            statement = connection.prepareStatement(PRODHIM_INSERT);
            statement.setInt(1, prodhim.getId());
            statement.setInt(2, prodhim.getPunetorID());
            statement.setInt(3, prodhim.getOperatorID());
            statement.setInt(4, prodhim.getProduktID());
            statement.setDouble(5, prodhim.getNrDozave());
            statement.setDouble(6, prodhim.getNrNjesive());
            statement.setDouble(7, prodhim.getProdhimIdeal());
            statement.setDouble(8, prodhim.getProdhimFakt());
            statement.setDouble(9,prodhim.getKostoFikse());
            statement.setDouble(10, prodhim.getKostoExtra());
            if (prodhim.getDataProdhimit()!= null){
                statement.setTimestamp(11, new Timestamp(prodhim.getDataProdhimit().getTime()));
            } else {
                statement.setNull(11, Types.TIMESTAMP);
            }
            if (prodhim.getDataVeprimit()!= null){
                statement.setTimestamp(12, new Timestamp(prodhim.getDataVeprimit().getTime()));
            } else {
                statement.setNull(12, Types.TIMESTAMP);
            }
            statement.setInt(13, prodhim.getPunishteID());
            statement.setInt(14, prodhim.getMagazinaID());
            statement.setString(15, prodhim.getShenime());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    private static int insertKonsum(Konsum konsum, Connection connection) throws SQLException {
        PreparedStatement statement = null;
        int rows = 0;
        if (connection == null || connection.isClosed()){
            return -1;
        }
        try {
            statement = connection.prepareStatement(PRODHIM_KONSUM_INSERT);
            statement.setInt(1, konsum.getProdhimID());
            statement.setInt(2, konsum.getArtikullID());
            statement.setDouble(3, konsum.getSasia());
            statement.setDouble(4, konsum.getCmimi());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static boolean saveProdhim(Prodhim prodhim){
        Connection connection = ConnectionFactory.getConnection();
        Savepoint startTransaction = null;
        try {
            connection.setAutoCommit(false);
            startTransaction = connection.setSavepoint();
            prodhim.setId(getNextID());
            int row = insert(prodhim, connection);
            if (row != 1){
                connection.rollback(startTransaction);
                return false;
            }
            List<Konsum> konsumList=prodhim.getKonsumList();
            for (Konsum konsum:konsumList){
                konsum.setProdhimID(prodhim.getId());
                row = insertKonsum(konsum, connection);
                if (row!=1){
                    connection.rollback(startTransaction);
                    return false;
                }
            }
           //TODO same thing per pagesat
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback(startTransaction);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        } finally {
            try{
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public static int update(Prodhim prodhim){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(PRODHIM_UPDATE);
            statement.setInt(1, prodhim.getPunetorID());
            statement.setInt(2, prodhim.getOperatorID());
            statement.setInt(3, prodhim.getProduktID());
            statement.setDouble(4, prodhim.getNrDozave());
            statement.setDouble(5, prodhim.getNrNjesive());
            statement.setDouble(6, prodhim.getProdhimIdeal());
            statement.setDouble(7, prodhim.getProdhimFakt());
            statement.setDouble(8,prodhim.getKostoFikse());
            statement.setDouble(9, prodhim.getKostoExtra());
            if (prodhim.getDataProdhimit()!= null){
                statement.setTimestamp(10, new Timestamp(prodhim.getDataProdhimit().getTime()));
            } else {
                statement.setNull(10, Types.TIMESTAMP);
            }
            if (prodhim.getDataVeprimit()!= null){
                statement.setTimestamp(11, new Timestamp(prodhim.getDataVeprimit().getTime()));
            } else {
                statement.setNull(11, Types.TIMESTAMP);
            }
            statement.setInt(12, prodhim.getPunishteID());
            statement.setInt(13, prodhim.getMagazinaID());
            statement.setString(14, prodhim.getShenime());
            statement.setInt(15, prodhim.getId());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static Prodhim selectSingleProdhim(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Prodhim prodhim = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_PRODHIM);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                prodhim = new Prodhim();
                prodhim.setId(resultSet.getInt(1));
                prodhim.setPunetorID(resultSet.getInt(2));
                prodhim.setOperatorID(resultSet.getInt(3));
                prodhim.setProduktID(resultSet.getInt(4));
                prodhim.setNrDozave(resultSet.getDouble(5));
                prodhim.setNrNjesive(resultSet.getDouble(6));
                prodhim.setProdhimIdeal(resultSet.getDouble(7));
                prodhim.setProdhimFakt(resultSet.getDouble(8));
                prodhim.setKostoFikse(resultSet.getDouble(9));
                prodhim.setKostoExtra(resultSet.getDouble(10));
                prodhim.setDataProdhimit(resultSet.getTimestamp(11));
                prodhim.setDataVeprimit(resultSet.getTimestamp(12));
                prodhim.setPunishteID(resultSet.getInt(13));
                prodhim.setMagazinaID(resultSet.getInt(14));
                prodhim.setShenime(resultSet.getString(15));
                prodhim.setKonsumList(getProdhimKonsumList(prodhim.getId()));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return prodhim;
    }

    public static List<Konsum> getProdhimKonsumList(int prodhimID){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Konsum> konsumList = new ArrayList<Konsum>();
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_PRODHIM_KONSUM);
            statement.setInt(1, prodhimID);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                Konsum konsum = new Konsum();
                konsum.setId(resultSet.getInt(1));
                konsum.setProdhimID(resultSet.getInt(2));
                konsum.setArtikullID(resultSet.getInt(3));
                konsum.setSasia(resultSet.getDouble(4));
                konsum.setCmimi(resultSet.getDouble(5));
                konsumList.add(konsum);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return konsumList;
    }


    private static int getNextID() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int result =0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement("SELECT nextval('prodhim_id_seq')");
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                result = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

}
